using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace GenerateSP
{
    public partial class Form1 : Form
    {
        //SqlConnection con = new SqlConnection(@"Data Source=ANKIT\SQLEXPRESS;Initial Catalog=Timitie&Sons;User ID=sa;Password=tgrpl;");
        private SqlConnection con = new SqlConnection();
        private string conStr;
        //SqlConnection con = new SqlConnection("Data Source=72.233.92.178,1799;Initial Catalog=gangashray;User ID=gangashray;Password=wer@lucid;");

        public Form1()
        {
            InitializeComponent();
        }

        private void btnTablesToExcel_Click(object sender, EventArgs e)
        {
        }

        //--Excel._Worksheet SetExcelHeader(Excel._Worksheet sht)
        //{
        //    string[] headerName = new string[] { "Field_Name", "Data_Type", "Size", "Is_Null", "Primary_Key", "Unique_Key", "Foreign_Key", "Auto_Increment", "Field_Index", "Other Constraint", "Default" };
        //    for (int i = 0; i < headerName.Length; i++)
        //    {
        //        sht.Cells[0, i] = headerName[i];
        //    }
        //    return sht;

        //}
        private void Form1_Load(object sender, EventArgs e)
        {
            ManageRadioButton();
            grpProcess.Enabled = false;
        }

        private void btnSPGenerate_Click(object sender, EventArgs e)
        {
            Generate("sp");
        }

        private void btnGenerateFN_Click(object sender, EventArgs e)
        {
            Generate("fn");
        }

        private void btnGenerateTR_Click(object sender, EventArgs e)
        {
            Generate("tr");
        }

        private void btnPrimaryKey_Click(object sender, EventArgs e)
        {
            DropConstraints();
        }

        private void btnView_Click(object sender, EventArgs e)
        {
            Generate("vi");
        }

        private void btnAll_Click(object sender, EventArgs e)
        {
            DropConstraints();
            btnDropTables_Click(sender, e);
            Generate("tr");
            Generate("sp");
            Generate("fn");
            Generate("vi");
        }

        private void Generate(string type)
        {
            Text = "Processing.... | SQL Server Script Generater V. 0.1";
            saveFileDialog1.Filter = "SQL File|*.sql";
            saveFileDialog1.DefaultExt = "sql";
            saveFileDialog1.ShowDialog();

            if (saveFileDialog1.FileName != null && saveFileDialog1.FileName != "")
            {
                try
                {
                    string Dtype = "";
                    var file = new FileStream(saveFileDialog1.FileName, FileMode.Create, FileAccess.Write);
                    var sw = new StreamWriter(file);
                    var dt = new DataTable();
                    con.Open();
                    var sda = new SqlDataAdapter();
                    if (type == "sp")
                    {
                        sda =
                            new SqlDataAdapter(
                                "SELECT NAME,object_definition(OBJECT_ID) AS data from sys.procedures ORDER BY create_date",
                                con);
                        Dtype = "PROCEDURE";
                    }
                    else if (type == "fn")
                    {
                        sda =
                            new SqlDataAdapter(
                                "SELECT NAME,object_definition(ID) AS data FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [crdate]",
                                con);
                        Dtype = "FUNCTION";
                    }
                    else if (type == "tr")
                    {
                        sda =
                            new SqlDataAdapter(
                                "SELECT NAME,object_definition(ID) AS DATA FROM sysobjects WHERE TYPE='TR' ORDER BY crdate ",
                                con);
                        Dtype = "TRIGGER";
                    }
                    else if (type == "vi")
                    {
                        sda =
                            new SqlDataAdapter(
                                "SELECT NAME,object_definition(ID) AS DATA FROM sys.views ORDER BY CREATE_DATE ", con);
                        Dtype = "VIEW";
                    }
                    sda.Fill(dt);
                    con.Close();
                    progressBar1.Minimum = 0;
                    progressBar1.Maximum = dt.Rows.Count;

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sw.WriteLine("------------------------------------------- " + Dtype + " : " +
                                     Convert.ToString(i + 1) + " --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id=OBJECT_ID('" +
                                     dt.Rows[i]["name"] + "'))");
                        sw.WriteLine("DROP " + Dtype + " " + dt.Rows[i]["name"]);
                        sw.WriteLine("GO");
                        sw.WriteLine(dt.Rows[i]["data"].ToString());
                        sw.WriteLine("GO");
                        progressBar1.Value = i;
                        lblProgCount.Text = Convert.ToString(i % progressBar1.Maximum) + "%";
                    }
                    sw.Close();
                    Text = "Done | SQL Server Script Generater V. 0.1";
                    progressBar1.Value = progressBar1.Maximum;
                    lblProgCount.Text = "Done";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        protected void DropConstraints()
        {
            Text = "Processing.... | SQL Server Script Generater V. 0.1";
            saveFileDialog1.Filter = "SQL File|*.sql";
            saveFileDialog1.DefaultExt = "sql";
            saveFileDialog1.ShowDialog();

            if (saveFileDialog1.FileName != null && saveFileDialog1.FileName != "")
            {
                try
                {
                    var file = new FileStream(saveFileDialog1.FileName, FileMode.Create, FileAccess.Write);
                    var sw = new StreamWriter(file);
                    var dtu = new DataTable();
                    var dtf = new DataTable();
                    var dtp = new DataTable();
                    var dtc = new DataTable();
                    int j = 0;

                    con.Open();

                    var sda = new SqlDataAdapter();

                    sda =
                        new SqlDataAdapter(
                            "SELECT  a.TABLE_NAME AS PRIMARY_TABLE, a.COLUMN_NAME,a.constraint_name FROM information_schema.constraint_column_usage A,INFORMATION_SCHEMA.TABLE_CONSTRAINTS b WHERE a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND CONSTRAINT_TYPE='UNIQUE'",
                            con);
                    sda.Fill(dtu);
                    sda =
                        new SqlDataAdapter(
                            "SELECT  a.TABLE_NAME AS PRIMARY_TABLE, a.COLUMN_NAME,a.constraint_name FROM information_schema.constraint_column_usage A,INFORMATION_SCHEMA.TABLE_CONSTRAINTS b WHERE a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND CONSTRAINT_TYPE='FOREIGN KEY'",
                            con);
                    sda.Fill(dtf);
                    sda =
                        new SqlDataAdapter(
                            "SELECT  a.TABLE_NAME AS PRIMARY_TABLE, a.COLUMN_NAME,a.constraint_name FROM information_schema.constraint_column_usage A,INFORMATION_SCHEMA.TABLE_CONSTRAINTS b WHERE a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND CONSTRAINT_TYPE='PRIMARY KEY'",
                            con);
                    sda.Fill(dtp);
                    sda =
                        new SqlDataAdapter(
                            "SELECT  a.TABLE_NAME AS PRIMARY_TABLE, a.COLUMN_NAME,a.constraint_name FROM information_schema.constraint_column_usage A,INFORMATION_SCHEMA.TABLE_CONSTRAINTS b WHERE a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND CONSTRAINT_TYPE='CHECK'",
                            con);
                    sda.Fill(dtc);
                    con.Close();
                    progressBar1.Minimum = 0;
                    progressBar1.Maximum = dtu.Rows.Count + dtf.Rows.Count + dtp.Rows.Count + dtc.Rows.Count;


                    sw.WriteLine("/*Script Generated with" + Environment.NewLine +
                                 "SQL Server Script Generater V. 0.3 by Kashyap Makadia - kashyap.makadia@gmail.com*/");
                    sw.WriteLine(Environment.NewLine);
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine("--------------------------------------------- UNIQUE KEY ----------------------------------------------");
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(Environment.NewLine);

                    for (int i = 0; i < dtu.Rows.Count; i++)
                    {
                        j++;
                        sw.WriteLine("------------------------------------------- Constraint : " + j + " --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('" + dtu.Rows[i][2] + "'))");
                        sw.Write("ALTER TABLE " + dtu.Rows[i][0] + " DROP CONSTRAINT " + dtu.Rows[i][2] + " " + Environment.NewLine + "GO" + Environment.NewLine);
                        progressBar1.Value = j;
                        lblProgCount.Text = Convert.ToString(j % progressBar1.Maximum) + "%";
                    }

                    sw.WriteLine(Environment.NewLine + Environment.NewLine);
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine("--------------------------------------------- FOREIGN KEY ---------------------------------------------");
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(Environment.NewLine);

                    for (int i = 0; i < dtf.Rows.Count; i++)
                    {
                        j++;
                        sw.WriteLine("------------------------------------------- Constraint : " + j +" --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('" + dtf.Rows[i][2] +"'))");
                        sw.Write("ALTER TABLE " + dtf.Rows[i][0] + " DROP CONSTRAINT " + dtf.Rows[i][2] + " " +Environment.NewLine + "GO" + Environment.NewLine);
                        progressBar1.Value = j;
                        lblProgCount.Text = Convert.ToString(j % progressBar1.Maximum) + "%";
                    }

                    sw.WriteLine(Environment.NewLine + Environment.NewLine);
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine("--------------------------------------------- PRIMARY KEY ---------------------------------------------");
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(Environment.NewLine);

                    for (int i = 0; i < dtp.Rows.Count; i++)
                    {
                        j++;
                        sw.WriteLine("------------------------------------------- Constraint : " + j +" --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('" + dtp.Rows[i][2] +"'))");
                        sw.Write("ALTER TABLE " + dtp.Rows[i][0] + " DROP CONSTRAINT " + dtp.Rows[i][2] + " " +Environment.NewLine + "GO" + Environment.NewLine);
                        progressBar1.Value = j;
                        lblProgCount.Text = Convert.ToString(j % progressBar1.Maximum) + "%";
                    }

                    sw.WriteLine(Environment.NewLine + Environment.NewLine);
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine("--------------------------------------------- CHECK KEY -----------------------------------------------");
                    sw.WriteLine("-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(Environment.NewLine);

                    for (int i = 0; i < dtc.Rows.Count; i++)
                    {
                        j++;
                        sw.WriteLine("------------------------------------------- Constraint : " + j +" --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('" + dtc.Rows[i][2] +"'))");
                        sw.Write("ALTER TABLE " + dtc.Rows[i][0] + " DROP CONSTRAINT " + dtc.Rows[i][2] + " " +Environment.NewLine + "GO" + Environment.NewLine);
                        progressBar1.Value = j;
                        lblProgCount.Text = Convert.ToString(j % progressBar1.Maximum) + "%";
                    }

                    sw.Close();
                    Text = "Done | SQL Server Script Generater V. 0.3";
                    progressBar1.Value = progressBar1.Maximum;
                    lblProgCount.Text = "Done";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void btnDropTables_Click(object sender, EventArgs e)
        {
            Text = "Processing.... | SQL Server Script Generater V. 0.1";
            saveFileDialog1.Filter = "SQL File|*.sql";
            saveFileDialog1.DefaultExt = "sql";
            saveFileDialog1.ShowDialog();

            if (saveFileDialog1.FileName != null && saveFileDialog1.FileName != "")
            {
                try
                {
                    var file = new FileStream(saveFileDialog1.FileName, FileMode.Create, FileAccess.Write);
                    var sw = new StreamWriter(file);
                    var dt = new DataTable();

                    con.Open();

                    var sda = new SqlDataAdapter("SELECT NAME FROM sys.tables ORDER BY create_date", con);
                    sda.Fill(dt);
                    con.Close();
                    progressBar1.Minimum = 0;
                    progressBar1.Maximum = dt.Rows.Count;
                    int j = 0;

                    sw.WriteLine(Environment.NewLine);
                    sw.WriteLine(
                        "-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(
                        "--------------------------------------------- DROP TALBES ---------------------------------------------");
                    sw.WriteLine(
                        "-------------------------------------------------------------------------------------------------------");
                    sw.WriteLine(Environment.NewLine);

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        j++;
                        sw.WriteLine("------------------------------------------- TABLE : " + j +
                                     " --------------------------------------------");
                        sw.WriteLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('" + dt.Rows[i][0] +
                                     "'))");
                        sw.Write("DROP TABLE " + dt.Rows[i][0] + " " + Environment.NewLine + "GO" + Environment.NewLine);
                        progressBar1.Value = j;
                        lblProgCount.Text = Convert.ToString(j % progressBar1.Maximum) + "%";
                    }
                    sw.Close();
                    Text = "Done | SQL Server Script Generater V. 0.1";
                    progressBar1.Value = progressBar1.Maximum;
                    lblProgCount.Text = "Done";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void rbLocalWin_CheckedChanged(object sender, EventArgs e)
        {
            ManageRadioButton();
        }

        private void rbLocalSQL_CheckedChanged(object sender, EventArgs e)
        {
            ManageRadioButton();
        }

        private void cmbLocalServer_Leave(object sender, EventArgs e)
        {
            GenerateConnectionString();
            GetDatabaseList(cmbLocalDb);
        }
        void ManageRadioButton()
        {

            if (rbLocalWin.Checked)
            {
                txtLocalUser.Enabled = false;
                txtLocalPass.Enabled = false;
            }
            else
            {
                txtLocalUser.Enabled = true;
                txtLocalPass.Enabled = true;
            }
        }
        private string GenerateConnectionString()
        {
            string conStr = string.Empty;
            var csb = new SqlConnectionStringBuilder();
            //csb.IntegratedSecurity = true;
            csb.PersistSecurityInfo = true;
            csb.DataSource = cmbLocalServer.Text.Trim();
            csb.InitialCatalog = cmbLocalDb.Text.Trim();
            if (rbLocalSQL.Checked)
            {
                csb.UserID = txtLocalUser.Text.Trim();
                csb.Password = txtLocalPass.Text.Trim();
                csb.PersistSecurityInfo = true;
            }
            else
            {
                csb.IntegratedSecurity = true;
            }
            this.conStr = csb.ConnectionString;
            return csb.ConnectionString;
        }

        public void GetDatabaseList(ComboBox cmb)
        {
            try
            {
                string sql = "select name from sys.databases order by name";
                DBManager.Common.DBManager db = new DBManager.Common.DBManager(this.conStr);
                cmb.DisplayMember = "name";
                if (db.IsError)
                    MessageBox.Show(db.ErrorMessage);
                cmb.DataSource = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
            }
            catch
            {
            }
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (btnConnect.Text == "Connect")
            {
                con = new SqlConnection(conStr);
                btnConnect.Text = "Disconnect";
                grpProcess.Enabled = true;
            }
            else
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
                btnConnect.Text = "Connect";
                grpProcess.Enabled = false;
            }
        }

    }
}